package tas.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import tas.dao.DaoArea;
import tas.sql.ConectaDb;

public class DaoAreaImpl implements DaoArea {

    private final ConectaDb db;

    public DaoAreaImpl() {
        this.db = new ConectaDb();
    }

    @Override
    public List<Object[]> areasQry(String filtro, long numpag, long filsXpag) {
        List<Object[]> list = null;

        String sql = "SELECT * FROM " // adicional a consulta
                + "(SELECT rownum rnum, a.* FROM ( " // adicional a consulta
                + "SELECT AREA.CODAREAINT, "
                + "  AREA.DESCRIPCIONAREAS, "
                + "  CASE WHEN ESTADOAREA='1' THEN 'ACTIVO' ELSE 'INACTIVO' END ESTADO "
                + " FROM ADMINPJ.V_PER_AREASPJ AREA "
                + " WHERE " + filtro
                + " ORDER BY 2"
                + ") a WHERE rownum <=?) " // adicional a consulta
                + "WHERE rnum > ?"; // adicional a consulta

        Connection cn = db.getBdtas();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);
                ps.setLong(1, ((1 + numpag) * filsXpag));
                ps.setLong(2, (numpag * filsXpag));

                ResultSet rs = ps.executeQuery();
                list = new LinkedList<Object[]>();

                while (rs.next()) {
                    Object[] f = new Object[3];
                    f[0] = rs.getObject(2);
                    f[1] = rs.getObject(3);
                    f[2] = rs.getObject(4);

                    list.add(f);
                }
            } catch (SQLException e) {
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                }
            }
        }
        return list;
    }

    @Override
    public List<Object[]> areasQryExc(String filtro) {
        List<Object[]> list = null;
        String sql = "SELECT AREA.CODAREAINT, "
                + "  AREA.DESCRIPCIONAREAS, "
                + "  CASE WHEN ESTADOAREA='1' THEN 'ACTIVO' ELSE 'INACTIVO' END ESTADO "
                + " FROM ADMINPJ.V_PER_AREASPJ AREA "
                + " WHERE " + filtro
                + " ORDER BY 2";

        Connection cn = db.getBdtas();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);
                
                ResultSet rs = ps.executeQuery();
                list = new LinkedList<Object[]>();

                while (rs.next()) {
                    Object[] f = new Object[3];
                    f[0] = rs.getObject(1);
                    f[1] = rs.getObject(2);
                    f[2] = rs.getObject(3);

                    list.add(f);
                }
            } catch (SQLException e) {
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                }
            }
        }
        return list;
    }
    
    @Override
    public Integer[] areasQryPag(String filtro, long filsXpag) {
        Integer[] result = null;

        String sql = "SELECT COUNT(*) "
                + " FROM ADMINPJ.V_PER_AREASPJ AREA "
                + " WHERE " + filtro;

        Connection cn = db.getBdtas();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);

                ResultSet rs = ps.executeQuery();

                if (rs.next()) {
                    // total filas de consulta
                    Integer totalfils = rs.getInt(1);

                    // cantidad de paginas con filsXpag
                    Integer ctasPags = (totalfils % filsXpag) == 0
                            ? (totalfils / (int) filsXpag)
                            : (totalfils / (int) filsXpag + 1);

                    result = new Integer[2];
                    result[0] = ctasPags;
                    result[1] = totalfils;
                }

            } catch (SQLException e) {
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                }
            }
        }
        return result;
    }
}
